#！/bin/bash
source /etc/profile
if [ -n "$1" ] 
then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
sql="
WITH tmp as(
SELECT 
device_id,
dt,
date_sub(dt,row_number() over(partition by device_id order by dt)) groupId
FROM dws.dws_member_start_day
WHERE dt between date_add('$do_date', -7) and '$do_date'),
tmp2 as(SELECT device_id,count(1) cnt 
FROM tmp
GROUP BY device_id,groupId
having cnt >= 3)
insert overwrite table ads.ads_member_continue_log
SELECT count(distinct device_id),date_add('$do_date', -7),'$do_date'
FROM tmp2;
"
hive -e "$sql"
